# ticket_system.py
import sqlite3
from datetime import datetime


class TicketSystem:
    def __init__(self, db_name='ticket_system.db'):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()
        self.create_tables()

    def create_tables(self):
        """创建数据库表结构"""
        # 创建店铺表
        self.cursor.execute('''
                            CREATE TABLE IF NOT EXISTS stores
                            (
                                store_id
                                INTEGER
                                PRIMARY
                                KEY,
                                store_name
                                TEXT
                                NOT
                                NULL,
                                location
                                TEXT
                            )
                            ''')

        # 创建客户表
        self.cursor.execute('''
                            CREATE TABLE IF NOT EXISTS customers
                            (
                                customer_id
                                INTEGER
                                PRIMARY
                                KEY,
                                name
                                TEXT
                                NOT
                                NULL,
                                phone
                                TEXT,
                                store_id
                                INTEGER,
                                FOREIGN
                                KEY
                            (
                                store_id
                            ) REFERENCES stores
                            (
                                store_id
                            )
                                )
                            ''')

        # 创建主题表
        self.cursor.execute('''
                            CREATE TABLE IF NOT EXISTS topics
                            (
                                topic_id
                                INTEGER
                                PRIMARY
                                KEY,
                                topic_name
                                TEXT
                                NOT
                                NULL,
                                department
                                TEXT
                            )
                            ''')

        # 创建工单表
        self.cursor.execute('''
                            CREATE TABLE IF NOT EXISTS tickets
                            (
                                ticket_id
                                INTEGER
                                PRIMARY
                                KEY,
                                customer_id
                                INTEGER
                                NOT
                                NULL,
                                topic_id
                                INTEGER
                                NOT
                                NULL,
                                created_at
                                DATETIME
                                DEFAULT
                                CURRENT_TIMESTAMP,
                                status
                                TEXT
                                CHECK (
                                status
                                IN
                            (
                                'Open',
                                'Pending',
                                'Resolved',
                                'Closed'
                            )) DEFAULT 'Open',
                                description TEXT,
                                FOREIGN KEY
                            (
                                customer_id
                            ) REFERENCES customers
                            (
                                customer_id
                            ),
                                FOREIGN KEY
                            (
                                topic_id
                            ) REFERENCES topics
                            (
                                topic_id
                            )
                                )
                            ''')

        # 创建索引优化查询性能
        self.cursor.execute('CREATE INDEX IF NOT EXISTS idx_customers_store ON customers(store_id)')
        self.cursor.execute('CREATE INDEX IF NOT EXISTS idx_tickets_customer ON tickets(customer_id)')
        self.cursor.execute('CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status)')

        self.conn.commit()

    def add_store(self, store_id, store_name, location):
        """添加新店铺"""
        self.cursor.execute(
            "INSERT INTO stores (store_id, store_name, location) VALUES (?, ?, ?)",
            (store_id, store_name, location)
        )
        self.conn.commit()

    def add_customer(self, customer_id, name, phone, store_id):
        """添加新客户"""
        # 验证店铺存在
        self.cursor.execute("SELECT 1 FROM stores WHERE store_id=?", (store_id,))
        if not self.cursor.fetchone():
            raise ValueError(f"店铺ID {store_id} 不存在")

        self.cursor.execute(
            "INSERT INTO customers (customer_id, name, phone, store_id) VALUES (?, ?, ?, ?)",
            (customer_id, name, phone, store_id)
        )
        self.conn.commit()

    def add_topic(self, topic_id, topic_name, department):
        """添加新主题"""
        self.cursor.execute(
            "INSERT INTO topics (topic_id, topic_name, department) VALUES (?, ?, ?)",
            (topic_id, topic_name, department)
        )
        self.conn.commit()

    def create_ticket(self, ticket_id, customer_id, topic_id, description):
        """创建新工单"""
        # 验证客户存在
        self.cursor.execute("SELECT 1 FROM customers WHERE customer_id=?", (customer_id,))
        if not self.cursor.fetchone():
            raise ValueError(f"客户ID {customer_id} 不存在")

        # 验证主题存在
        self.cursor.execute("SELECT 1 FROM topics WHERE topic_id=?", (topic_id,))
        if not self.cursor.fetchone():
            raise ValueError(f"主题ID {topic_id} 不存在")

        self.cursor.execute(
            "INSERT INTO tickets (ticket_id, customer_id, topic_id, description) VALUES (?, ?, ?, ?)",
            (ticket_id, customer_id, topic_id, description)
        )
        self.conn.commit()

    def get_ticket_details(self, ticket_id):
        """获取工单详情（包含客户、店铺和主题信息）"""
        query = '''
                SELECT t.ticket_id, \
                       c.name     AS customer_name, \
                       c.phone, \
                       s.store_name, \
                       s.location AS store_location, \
                       tp.topic_name, \
                       tp.department, \
                       t.created_at, \
                       t.status, \
                       t.description
                FROM tickets t
                         JOIN customers c ON t.customer_id = c.customer_id
                         JOIN stores s ON c.store_id = s.store_id
                         JOIN topics tp ON t.topic_id = tp.topic_id
                WHERE t.ticket_id = ? \
                '''
        self.cursor.execute(query, (ticket_id,))
        return self.cursor.fetchone()

    def get_store_tickets(self, store_id, status=None):
        """获取指定店铺的所有工单"""
        query = '''
                SELECT t.ticket_id, \
                       c.name AS customer_name, \
                       tp.topic_name, \
                       t.created_at, \
                       t.status
                FROM tickets t
                         JOIN customers c ON t.customer_id = c.customer_id
                         JOIN topics tp ON t.topic_id = tp.topic_id
                WHERE c.store_id = ? \
                '''
        params = [store_id]

        if status:
            query += " AND t.status = ?"
            params.append(status)

        query += " ORDER BY t.created_at DESC"

        self.cursor.execute(query, tuple(params))
        return self.cursor.fetchall()

    def update_ticket_status(self, ticket_id, new_status):
        """更新工单状态"""
        valid_statuses = ['Open', 'Pending', 'Resolved', 'Closed']
        if new_status not in valid_statuses:
            raise ValueError(f"无效状态: {new_status}. 有效状态: {', '.join(valid_statuses)}")

        self.cursor.execute(
            "UPDATE tickets SET status = ? WHERE ticket_id = ?",
            (new_status, ticket_id)
        )
        self.conn.commit()
        return self.cursor.rowcount > 0

    def get_tickets_by_topic(self, topic_id=None):
        """按主题统计工单数量"""
        query = '''
                SELECT tp.topic_name, \
                       COUNT(t.ticket_id)                                                            AS ticket_count, \
                       SUM(CASE WHEN t.status = 'Open' THEN 1 ELSE 0 END)                            AS open_count, \
                       SUM(CASE WHEN t.status = 'Resolved' OR t.status = 'Closed' THEN 1 ELSE 0 END) AS closed_count
                FROM topics tp
                         LEFT JOIN tickets t ON tp.topic_id = t.topic_id \
                '''
        params = []

        if topic_id:
            query += " WHERE tp.topic_id = ?"
            params.append(topic_id)

        query += " GROUP BY tp.topic_name ORDER BY ticket_count DESC"

        self.cursor.execute(query, tuple(params))
        return self.cursor.fetchall()

    def close(self):
        """关闭数据库连接"""
        self.conn.close()

    def __del__(self):
        self.close()


def initialize_sample_data(system):
    """初始化示例数据"""
    # 添加店铺
    stores = [
        (1, '北京旗舰店', '北京市朝阳区建国路88号'),
        (2, '上海体验店', '上海市浦东新区张江高科技园区'),
        (3, '广州分店', '广州市天河区体育西路189号')
    ]
    for store in stores:
        system.add_store(*store)

    # 添加客户
    customers = [
        (101, '张明', '13800138000', 1),
        (102, '李芳', '13900139000', 2),
        (103, '王强', '13700137000', 1),
        (104, '赵静', '13600136000', 3)
    ]
    for customer in customers:
        system.add_customer(*customer)

    # 添加主题
    topics = [
        (1001, '产品质量问题', '售后部'),
        (1002, '订单咨询', '客服部'),
        (1003, '退货申请', '售后部'),
        (1004, '发票问题', '财务部')
    ]
    for topic in topics:
        system.add_topic(*topic)

    # 创建工单
    tickets = [
        (5001, 101, 1001, '新买的手机屏幕有划痕'),
        (5002, 102, 1002, '订单#A12345的发货状态查询'),
        (5003, 103, 1001, '耳机有杂音，要求更换'),
        (5004, 101, 1004, '需要补开上个月的发票'),
        (5005, 104, 1003, '购买的衣服尺寸不合适，申请退货'),
        (5006, 102, 1002, '促销活动咨询'),
        (5007, 103, 1004, '发票信息填写错误，需要修改')
    ]
    for ticket in tickets:
        system.create_ticket(*ticket)

    # 更新部分工单状态
    system.update_ticket_status(5002, 'Resolved')
    system.update_ticket_status(5005, 'Closed')


def main():
    # 创建工单系统实例
    system = TicketSystem()

    # 初始化示例数据
    initialize_sample_data(system)

    print("=" * 50)
    print("工单系统演示")
    print("=" * 50)

    # 示例1: 查询特定工单详情
    ticket_id = 5001
    details = system.get_ticket_details(ticket_id)
    print(f"\n工单 #{ticket_id} 详情:")
    print(f"客户: {details[1]} ({details[2]})")
    print(f"店铺: {details[3]} ({details[4]})")
    print(f"主题: {details[5]} ({details[6]})")
    print(f"创建时间: {details[7]}")
    print(f"状态: {details[8]}")
    print(f"问题描述: {details[9]}")

    # 示例2: 查询北京旗舰店的工单
    store_id = 1
    print(f"\n北京旗舰店 (ID:{store_id}) 的所有工单:")
    store_tickets = system.get_store_tickets(store_id)
    for ticket in store_tickets:
        print(f"工单#{ticket[0]} | 客户: {ticket[1]} | 主题: {ticket[2]} | 创建时间: {ticket[3]} | 状态: {ticket[4]}")

    # 示例3: 按主题统计工单
    print("\n按主题统计工单数量:")
    topic_stats = system.get_tickets_by_topic()
    for stat in topic_stats:
        print(f"{stat[0]}: 总工单 {stat[1]} | 未解决: {stat[2]} | 已解决: {stat[3]}")

    # 示例4: 更新工单状态
    ticket_id = 5001
    if system.update_ticket_status(ticket_id, 'Resolved'):
        updated = system.get_ticket_details(ticket_id)
        print(f"\n更新工单 #{ticket_id} 状态为 '{updated[8]}'")

    # 关闭系统
    system.close()


if __name__ == "__main__":
    main()